package dao;



import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import dao.BookDao;
import dbs.DataBaseConnection;
import vo.Passenger;
import vo.RTicket;
import vo.TicketIn;
import vo.TicketNum;

public class BookDaoImpl implements BookDao{
	@Override
	public List queryBySta(String tid) throws Exception {
			List all = new ArrayList();
			PreparedStatement pstmt = null;
	        DataBaseConnection dbs = new DataBaseConnection();
	        String sql = "exec proc_ticketin ?";		
	        try {
	        	pstmt = dbs.getConnection().prepareStatement(sql);
	        	pstmt.setString(1,tid);
	        	ResultSet rs = pstmt.executeQuery();
	        	while(rs.next()) {
	        		TicketIn ti = new TicketIn();
	        		ti.setTId(rs.getString(1));
	        		ti.setSetSta(rs.getString(2));
	        		ti.setArSta(rs.getString(3));
	        		ti.setSetDate(rs.getString(4));
	        		ti.setUseTime(rs.getFloat(5));
	        		ti.setSeatL(rs.getString(6));
	        		ti.setCarr(rs.getInt(7));
	        		ti.setSeatN(rs.getString(8));
	        		ti.setPrice(rs.getFloat(9));
	        		System.out.println(rs.getFloat(9));
	        		all.add(ti);
	        	}
	        	rs.close();
	        	pstmt.close();
	        }catch(Exception e4) {
	        	e4.printStackTrace();
	        }finally {
	        	dbs.close();
	        }
	        return all;
		
	}

	@Override
	public void bookT(String tid,int carr,String seatn) throws Exception {
		PreparedStatement pstmt = null;
        DataBaseConnection dbs = new DataBaseConnection();
        String sql = "insert into ReTicket select * from TicketIn where tid=? and carr=? and seatn=?";		
        try{
			pstmt = dbs.getConnection().prepareStatement(sql);
        	pstmt.setString(1,tid );
		    pstmt.setInt(2, carr);
		    pstmt.setString(3, seatn);
		    pstmt.executeUpdate();
		    pstmt.close();
		 }catch(Exception e) {
			    System.out.println("出错了");
	        	e.printStackTrace();
	        }finally{
	        	if( dbs!=null) {
	        	    dbs.close();
	        	    dbs = null;
	        	}
	        }
	}

	@Override
	public void insertB(int uid, int pid, String tid, int carr,String seatn){
		PreparedStatement pstmt = null;
        DataBaseConnection dbs = new DataBaseConnection();
        String sql = "insert into Book values(?,?,?,?,?,?)";
		try{
			pstmt = dbs.getConnection().prepareStatement(sql);
        	pstmt.setInt(1, uid);
		    pstmt.setInt(2, pid);
		    pstmt.setString(3, tid);
		    pstmt.setInt(4, carr);
		    pstmt.setString(5, seatn);
		    pstmt.setDate(6,new Date(carr));
		    pstmt.executeUpdate();
		    pstmt.close();
		 }catch(Exception e) {
	        	e.printStackTrace();
	        }finally{
	        	if( dbs!=null) {
	        	    dbs.close();
	        	    dbs = null;
	        	}
	        }
	}

	@Override
	public void insertP(Passenger p) {
		PreparedStatement pstmt = null;
        DataBaseConnection dbs = new DataBaseConnection();
        String sql = "insert into Passenger values(?,?,?,?)";
        try {
        	pstmt = dbs.getConnection().prepareStatement(sql);
        	pstmt.setString(1, p.getPName());
		    pstmt.setString(2, p.getPIdtype());
		    pstmt.setString(3, p.getPIDnum());
		    pstmt.setString(4, p.getPPhone());
		    pstmt.executeUpdate();
		    pstmt.close();
        }catch(Exception e) {
        	e.printStackTrace();
        }finally{
        	if(dbs!=null) {
        		dbs.close();
        		dbs = null;
        	}
        }
	}

	@Override
	public void unsubT(String tid,int carr,String seatn) throws Exception {
		PreparedStatement pstmt = null;
		DataBaseConnection dbs = new DataBaseConnection();
		String sql = " exec proc_r_p_b ?,?,?"; 
		try {
			pstmt = dbs.getConnection().prepareStatement(sql);
			pstmt.setString(1, tid);
			pstmt.setInt(2, carr);
			pstmt.setString(3, seatn);
			pstmt.executeUpdate();
	    pstmt.close();
		}catch(Exception e) {
			System.out.println("出现操作异常");
		}finally {
			if(dbs!=null) {
			dbs.close();
			dbs = null;
			}
		}
	}

	@Override
	public int findPid(String PIDnum) throws Exception {
		int pid = 0;
		PreparedStatement pstmt = null;
		DataBaseConnection dbs = new DataBaseConnection();
		String sql = "select pid from passenger where pidnum=?"; 
		try {
			pstmt = dbs.getConnection().prepareStatement(sql);
			pstmt.setString(1, PIDnum);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()) {
				pid = rs.getInt(1);
			}
	    rs.close();
	    pstmt.close();
		}catch(Exception e) {
			System.out.println("出现操作异常");
		}finally {
			if(dbs!=null) {
			dbs.close();
			dbs = null;
			}
		}
		return pid;
	}

	@Override
	public List queryTInfo(int uid)
			throws Exception {
		
		List all = new ArrayList();
		PreparedStatement pstmt = null;
        DataBaseConnection dbs = new DataBaseConnection();
        String sql = "exec proc_book ?";		
        try {
        	pstmt = dbs.getConnection().prepareStatement(sql);
        	pstmt.setInt(1,uid);
        	ResultSet rs = pstmt.executeQuery();
        	while(rs.next()) {
        		RTicket rt = new RTicket();
        		rt.setPName(rs.getString(1));
        		rt.setPIdtype(rs.getString(2));
        		rt.setTId(rs.getString(3));
        		rt.setSetSta(rs.getString(4));
        		rt.setArSta(rs.getString(5));
        		rt.setSetDate(rs.getString(6));
        		rt.setUseTime(rs.getFloat(7));
        		rt.setSeatL(rs.getString(8));
        		rt.setCarr(rs.getInt(9));
        		rt.setSeatN(rs.getString(10));
        		rt.setPrice(rs.getFloat(11));
             	all.add(rt);
        	}
        	rs.close();
        	pstmt.close();
        }catch(Exception e4) {
        	e4.printStackTrace();
        }finally {
        	dbs.close();
        }
        return all;
	}

	@Override
	public List queryByNum(String setSta, String arSta) throws Exception {
		List all = new ArrayList();
		PreparedStatement pstmt = null;
        DataBaseConnection dbs = new DataBaseConnection();
        String sql = "exec proc_ticketnum ?,?";		
        try {
        	pstmt = dbs.getConnection().prepareStatement(sql);
        	pstmt.setString(1,setSta);
        	pstmt.setString(2,arSta);
        	ResultSet rs = pstmt.executeQuery();
        	while(rs.next()) {
        		TicketNum tn = new TicketNum();
        		tn.setTId(rs.getString(1));
        		tn.setSetSta(rs.getString(2));
        		tn.setArSta(rs.getString(3));
        		tn.setSetDate(rs.getString(4));
        		tn.setUseTime(rs.getFloat(5));
        	    tn.setTNum(rs.getInt(6));
        		all.add(tn);
        	}
        	rs.close();
        	pstmt.close();
        }catch(Exception e4) {
        	e4.printStackTrace();
        }finally {
        	dbs.close();
        }
        return all;
	}
    
}
